from flask import Flask,render_template,request,redirect,url_for,session,jsonify,abort
from functools import wraps
import uuid,pymysql,os,hashlib

app = Flask(__name__)
app.secret_key = str(uuid.uuid4())

class MySQLServer:
    def __init__(self, host='localhost', port=3306, user='root', password=None):
        self.host = host
        self.port = port
        self.user = user
        self.password = password or os.getenv('mysql_password')
        self.connection = None

    def connect(self):
        self.connection = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
        )

    def close(self):
        if self.connection:
            self.connection.close()

    def create_database(self, db_name):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(f'CREATE DATABASE IF NOT EXISTS {db_name}')
            self.connection.commit()
        except pymysql.MySQLError as e:
            self.connection.rollback()
            print(f"Error: {e}")

    def use_database(self, db_name):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(f'USE {db_name}')
        except pymysql.MySQLError as e:
            print(f"Error: {e}")

    def create_table(self, table_name, columns):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(f'CREATE TABLE IF NOT EXISTS {table_name} ({columns})')
            self.connection.commit()
        except pymysql.MySQLError as e:
            self.connection.rollback()
            print(f"Error: {e}")

    def insert_data(self, table_name, data):
        try:
            with self.connection.cursor() as cursor:
                placeholders = ', '.join(['%s'] * len(data))
                columns = ', '.join(data.keys())
                cursor.execute(f'INSERT INTO {table_name} ({columns}) VALUES ({placeholders})', list(data.values()))
            self.connection.commit()
        except pymysql.MySQLError as e:
            self.connection.rollback()
            print(f"Error: {e}")

    def register_user(self, username, password, user_type):
        self.connect()
        self.create_database('test_sys')
        self.use_database('test_sys')

        if user_type == 'student':
            self.create_table('student_info', 'user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL')
            self.connection.cursor().execute(f'ALTER TABLE student_info AUTO_INCREMENT = 1000')
            self.insert_data('student_info', {'username': username, 'password': password})
        elif user_type == 'teacher':
            self.create_table('teacher_info', 'user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL')
            self.connection.cursor().execute(f'ALTER TABLE teacher_info AUTO_INCREMENT = 100')
            self.insert_data('teacher_info', {'username': username, 'password': password})
        else:
            return jsonify({"status": "error", "message": "未知的错误！"})
        
        self.close()
    
    def login_user(self, username, password, user_type):
        self.connect()
        with self.connection.cursor() as cursor:
            try:
                self.use_database('test_sys')
                if user_type == 'student':
                    cursor.execute(f'select username, password from student_info where username = %s', (username,))
                    info = cursor.fetchone()
                    if info:
                        if info[1] == password:
                            session['username'] = username
                            session['user_type'] = user_type
                            return jsonify({"status": "success", "message": "登录成功！"})
                        else:
                            return jsonify({"status": "error", "message": "密码错误！"})
                    else:
                        return jsonify({"status": "error", "message": "不存在该账户！请先注册！"})
                elif user_type == 'teacher':
                    cursor.execute(f'select username, password from teacher_info where username = %s', (username,))
                    info = cursor.fetchone()
                    if info:
                        if info[1] == password:
                            session['username'] = username
                            session['user_type'] = user_type
                            return jsonify({"status": "success", "message": "登录成功！"})
                        else:
                            return jsonify({"status": "error", "message": "密码错误！"})
                    else:
                        return jsonify({"status": "error", "message": "不存在该账户！请先注册！"})
            except Exception as e:
                print(f"Error: {e}")
                return jsonify({"status": "error", "message": "首次使用请先注册！"})
        self.close()

    def course_info(self,action_type,course_name=None):
        self.connect()
        self.use_database('test_sys')
        self.create_table('course_info','course_id INT AUTO_INCREMENT PRIMARY KEY,course_name VARCHAR(255) NOT NULL')
        self.connection.cursor().execute(f'ALTER TABLE course_info AUTO_INCREMENT = 100')
        try:
            if action_type == 'add' :
                self.insert_data('course_info',{'course_name':course_name})
            elif action_type == 'show' :
                with self.connection.cursor() as cursor:
                    cursor.execute('select * from course_info')
                    data = [item for item in cursor.fetchall()]
                return jsonify(data) 
        except Exception as e:
            print(f"Error: {e}")
            return jsonify({"status": "error", "message": "未知的错误！"})

    def grades_info(self,action_type,student_name=None,course_name=None,grade=None):
        self.connect()
        self.use_database('test_sys')
        self.create_table('grades_info','student_id INT,course_id INT,grade DECIMAL(5,2) CHECK (grade >= 0 AND grade <= 100),PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES student_info(user_id),FOREIGN KEY (course_id) REFERENCES course_info(course_id)')
        try:
            with self.connection.cursor() as cursor:
                cursor.execute('select user_id from student_info where username = %s',(student_name,))
                student_id = cursor.fetchone()[0]
                if action_type == 'add':
                    cursor.execute('select course_id from course_info where course_name = %s',(course_name,))
                    course_id = cursor.fetchone()[0]
                    self.insert_data('grades_info',{'student_id':student_id,'course_id':course_id,'grade':grade})
                elif action_type == 'show' :
                    with self.connection.cursor() as cursor:
                        if student_name:
                            cursor.execute('''
                                SELECT s.username,c.course_name,g.grade
                                FROM student_info as s
                                JOIN grades_info as g on g.student_id = s.user_id
                                JOIN course_info as c on c.course_id = g.course_id
                                where s.username = %s
                            ''',(student_name,))
                            data = [item for item in cursor.fetchall()]
                            return jsonify(data)
                        else:
                            cursor.execute('''
                                SELECT s.username,c.course_name,g.grade
                                FROM student_info as s
                                JOIN grades_info as g on g.student_id = s.user_id
                                JOIN course_info as c on c.course_id = g.course_id
                            ''')
                            data = [item for item in cursor.fetchall()]
                            return jsonify(data)
        except Exception as e:
            print(f"Error: {e}")
            return jsonify({"status": "error", "message": "未知的错误！"})
    
    def user_info(self,user_type):
        self.connect()
        self.use_database('test_sys')
        try:
            if user_type == 'student':
                with self.connection.cursor() as cursor:
                    cursor.execute('select user_id,username from student_info')
                data = [{'id': item[0], 'name': item[1]} for item in cursor.fetchall()]
                return jsonify(data) 
            elif user_type == 'teacher':
                with self.connection.cursor() as cursor:
                    cursor.execute('select user_id,username from teacher_info')
                data = [{'id': item[0], 'name': item[1]} for item in cursor.fetchall()]
                return jsonify(data) 
        except Exception as e:
            print(f"Error: {e}")
            return jsonify({"status": "error", "message": "未知的错误！"})

@app.route("/")
def root():
    return redirect(url_for('login'))

def internal_only(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not request.headers.get('X-Internal-Request'):
            abort(403)  # Forbidden
        return f(*args, **kwargs)
    return decorated_function

@app.route("/home")
def home():
    username = session.get('username')
    user_type = session.get('user_type')
    if username and user_type:
        return render_template('home.html', username=username, user_type=user_type)
    else:
        return redirect(url_for('login'))

@app.route("/login",methods=['GET','POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        user_type = request.form.get('user_type')
        ms = MySQLServer()
        info = ms.login_user(username=username,password=password,user_type=user_type)
        return info
    elif request.method == 'GET':
        return render_template("login.html")
    else:
        return "未设置的请求！"

@app.route('/logout')
def logout():
    session.pop('username', None)
    session.pop('user_type', None)
    return redirect(url_for('login'))

@app.route("/register",methods=['GET','POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        hash_password = hashlib.sha256(request.form.get('password').encode()).hexdigest()
        user_type = request.form.get('user_type')
        ms = MySQLServer()
        try:
            ms.register_user(username=username,password=password,user_type=user_type)
            return jsonify({"status": "success", "message": "注册成功！"})
        except Exception as e:
            print(f'发生了一个错误： {e}')
            return jsonify({"status": "error", "message": "注册失败！"})
    return jsonify({'user_type':user_type,'username':username,'password':hash_password})

@app.route('/show_students')
@internal_only
def show_students():
    ms = MySQLServer()
    return  ms.user_info('student')

@app.route('/show_course')
@internal_only
def show_course():
    ms = MySQLServer()
    return ms.course_info('show')

@app.route('/show_grades',methods=['POST'])
@internal_only
def show_grades():
    ms = MySQLServer()
    name = request.json['name']
    return ms.grades_info('show',name)

@app.route('/get_course_info',methods=['POST'])
@internal_only
def get_course_info():
    try:
        data = request.get_json()
        courses = data.get('courses',[])
        ms = MySQLServer()
        for course in courses:
            ms.course_info('add', course)
        return jsonify({"status": "success", "message": "添加成功！"})
    except Exception as e:
        print(e)
        return jsonify({"status": "error", "message": "添加失败！"})

@app.route('/get_score_info',methods=['POST'])
def get_score_info():
    try:
        info = request.get_json().get('info',[])
        ms = MySQLServer()
        for i in info:
            ms.grades_info('add',i['name'],i['course'],i['grade'])
        return jsonify({"status": "success", "message": "添加成功！"})
    except Exception as e:
        print(e)
        return jsonify({"status": "error", "message": "添加失败！"})
if __name__ == '__main__':
    app.run(debug=True)